﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    /// <summary>
    /// 通用数据库访问类
    /// </summary>
    public class DBHelper
    {
        //连接配置文件的数据库参数生成数据库连接字符串
        private static string str = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
        //建立数据库连接对象
        public static SqlConnection conn = new SqlConnection(str);

        // 执行数据库查询，返回意向数据结果
        public static object GetScalar(SqlCommand cmd)
        {
            cmd.Connection = conn; //建立数据库对象连接
            object ob = new SqlCommand();
            try
            {
                if(conn.State== ConnectionState.Closed)
                {
                    conn.Open();//打开数据库连接
                }
                ob = cmd.ExecuteScalar();//调用SqlCommand对象的ExcuteScalar方法访问数据库
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return ob;
        }
        //
        public static DataSet GetDataSet(SqlCommand cmd)
        {
            conn = new SqlConnection(str);
            cmd.Connection = conn;
            SqlDataAdapter dt = new SqlDataAdapter(cmd);//实例化一个数据适配器，完成数据桥接，数据适配器用于填充数据集
            DataSet ds = new DataSet();
            try
            {
                dt.Fill(ds);//将数据适配器中的内容填充至数据集
            }
            catch(Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return ds;
        }
        /// <summary>
        /// 数据库修改操作方法实现
        /// </summary>
        public static int ExecuteSQL(SqlCommand cmd)
        {
            conn= new SqlConnection(str);//初始化数据库连接
            cmd.Connection = conn;//建立数据库连接
            if (conn.State == ConnectionState.Closed)//判断数据库是否打开连接
                conn.Open();
            int num = 0;
            try
            {
                num = cmd.ExecuteNonQuery();//调用SqlCommand对象的ExecuteNonQuery()方法
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)//判断数据库是否打开连接
                    conn.Close();
            }
            return num;
        }
        /// <summary>
        /// 读取数据库，并提供对查询数据结果的影响行数
        /// </summary>
        public static SqlDataReader ExcuteReader(SqlCommand cmd)
        {
            if (conn.State == ConnectionState.Closed)//判断数据库是否打开连接
                conn.Open();
            cmd.Connection = conn;
            SqlDataReader read;
            try
            {
                //执行数据库查询命令，并在执行完毕关闭数据库连接
                read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return read;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
    }
}
